package com.vecspace.msoffice;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;

import com.vecspace.exception.HException;
import com.vecspace.utils.FileHelper;
import com.vecspace.utils.UUIDUtil;
import com.vecspace.utils.log.HLogger;

public class ExportToHSSF {
	
	public enum DataType{
		/**
		 * 基本信息,模板文件中单元格内容以$$开头，格式如$${total}
		 */
		BASEINFO,
		/**
		 * 业务数据,模板文件中单元格内容以$开头，格式如${name}
		 */
		BUSSINESSDATA
	}
	
	private List<Integer> mergedList = new ArrayList<Integer>();
	private int dataIndex=0;
	
	
	/**
	 * 
	 * 功能描述：
	 * @param templateExcelFileName 模板文件(.xls格式 office2003)
	 * @param tempFilePath 存放临时文件的路径
	 * @param rows 要导出的循环数据行（浮动行数据）
	 * @param data 要导出的非循环行数据（比如表单数据）
	 * @throws Exception
	 */
	public String export2Excel(String templateExcelFileName, String tempFilePath, List<?> rows, Object data,boolean needMerge) throws Exception{
		if(templateExcelFileName == null || !templateExcelFileName.toLowerCase().endsWith(".xls")){
			throw new HException("模板文件必须是Excel2003格式的");
		}
		String uuid = UUIDUtil.createUUID();
		if(!tempFilePath.endsWith("/") && !tempFilePath.endsWith("\\")){
			tempFilePath = tempFilePath + File.separator;
		}
		File path = new File(tempFilePath);
		if(!path.exists()) {
			path.mkdirs();
			HLogger.DEFAULT().info("Excel导出文件临时目录：" + path.getAbsolutePath());
		}
		String outFileName = tempFilePath + uuid + ".xls";
		FileOutputStream fileOut = new FileOutputStream(outFileName);
		try{
			writeByTemlate(templateExcelFileName, fileOut, rows, data,needMerge);
		}finally{
			fileOut.close();
		}
		return outFileName;
	}
	
	public void writeByTemlate(String templateName, OutputStream out, List<?> data,Object root,boolean needMerge)
			throws Exception {
		String fileName = "templates/"+templateName;
//		String fileName2 = FileHelper.getAbsoluteFilePath(fileName);
//		File file = new File(fileName2);
//		if(!file.exists()){
//			throw new HException("模板文件不存在(F):" + file.getAbsolutePath());
//		}
		InputStream resourceAsStream = FileHelper.openFile(fileName);
		if(resourceAsStream == null){
			throw new HException("模板文件不存在(S):" + fileName);
		}
		POIFSFileSystem fs = new POIFSFileSystem(resourceAsStream);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		try {
			HSSFSheet sheet = wb.getSheetAt(0);
			int start = sheet.getFirstRowNum();
			int end = sheet.getLastRowNum();
			int startloop = -1;
			int endloop = end;
			int rownum = start;
			for (; rownum <= end; rownum++) {
				Row row = sheet.getRow(rownum);
				Cell cell = row == null ? null : row.getCell(0);
				if (cell != null && cell.getCellType() ==CellType.STRING) {
					String value = cell.getStringCellValue();
					if ("startloop".equalsIgnoreCase(value)) {
						startloop = rownum + 1;
					} else if ("endloop".equalsIgnoreCase(value)) {
						endloop = rownum - 1;
					}
				}
			}
			
			if(root==null){
				root=new HashMap<String, Object>();
			}
			if(data==null){
				data=new ArrayList<Object>();
			}
			//写基本信息
			for(rownum=start; rownum < end + 1; rownum++){
				setValue(sheet.getRow(rownum),root, DataType.BASEINFO);
			}
			//写循环数据
			rownum = endloop + 1;//sheet.getLastRowNum();
			for(Object obj:data){
				dataIndex++;
				rownum = copyRows(sheet, startloop, endloop, ++rownum);
				for(int i=0;i<endloop-startloop+1;i++){
					setValue(sheet.getRow(rownum-i),obj, DataType.BUSSINESSDATA);
				}
			}
			removeRow(sheet, startloop - 1, endloop + 1);
			if(needMerge){
				//mergeCell(sheet, 0, 0, 0, 0);
			}
			wb.write(out);
		}finally {
			wb.close();
			fs.close();
		}
	}

	public void setValue(HSSFRow row,Object data, DataType dataType){
		HSSFCell cell=null;
		String value=null;
		if(row == null) {
			return;
		}
		for(int i=0,len=row.getLastCellNum();i<=len;i++){
			cell = row.getCell(i);
			if(cell!=null&&cell.getCellType()== CellType.STRING){
				value=cell.getStringCellValue();
				value=value==null?"":value.trim();
				// 防止处理基本信息时将业务数据表达式处理掉，加以区分处理
				if (dataType.equals(DataType.BASEINFO)){
					if(value.indexOf("$${")==0){
						setCellValue(cell, getValue(data, value.substring(3,value.length()-1)));
					}
				} else if(dataType.equals(DataType.BUSSINESSDATA)){
					if("${rowid}".equalsIgnoreCase(value)){
						setCellValue(cell, dataIndex);
					}else if(value.indexOf("${")==0){
						setCellValue(cell, getValue(data, value.substring(2,value.length()-1)));
					}
				}				
			}
		}
	}
	
	/**
	 * 复制行
	 * 
	 * @param currentSheet
	 *            工作表
	 * @param startRowIndex
	 *            起始行
	 * @param endRowIndex
	 *            结束行
	 * @param pPosition
	 *            目标起始行位置
	 */
	private int copyRows(HSSFSheet currentSheet, int pStartRow, int pEndRow,
			int pPosition) {
		if (pStartRow == -1 || pEndRow == -1) {
			return pPosition;
		}
		
		int columnCount;
		// 复制单元格式数据
		for (int i = pStartRow; i <= pEndRow; i++) {
			HSSFRow sourceRow = currentSheet.getRow(i);
			if (sourceRow != null) {
				columnCount = sourceRow.getLastCellNum();	
				int insertRowNum = pPosition - pStartRow + i;
				if(insertRowNum < currentSheet.getLastRowNum()){
					currentSheet.shiftRows(insertRowNum, currentSheet.getLastRowNum(), 1,true,false);//将最后的空行移到需要插入行的位置
				}
				HSSFRow newRow = currentSheet.createRow(insertRowNum);
				for (int j = 0; j < columnCount; j++) {
					HSSFCell templateCell = sourceRow.getCell(j);
					if (templateCell != null) {
						HSSFCell newCell = newRow.createCell(j);
						copyCell(templateCell, newCell);
					}
				}
				copyRow(sourceRow, newRow);
			}
		}
		
		int targetRowFrom = 0, targetRowTo = 0;
		CellRangeAddress region = null;
		// 复制合并区域
		if (mergedList.size() > 0) {
			for (int i : mergedList) {
				region = currentSheet.getMergedRegion(i);
				targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
				targetRowTo = region.getLastRow() - pStartRow + pPosition;
				CellRangeAddress newRegion = region.copy();
				newRegion.setFirstRow(targetRowFrom);
				newRegion.setLastRow(targetRowTo);
				currentSheet.addMergedRegion(newRegion);
			}
		} else {
			for (int i = 0, len = currentSheet.getNumMergedRegions(); i < len; i++) {
				region = currentSheet.getMergedRegion(i);
				if ((region.getFirstRow() >= pStartRow)
						&& (region.getLastRow() <= pEndRow)) {
					targetRowFrom = region.getFirstRow() - pStartRow
							+ pPosition;
					targetRowTo = region.getLastRow() - pStartRow + pPosition;
					CellRangeAddress newRegion = region.copy();
					newRegion.setFirstRow(targetRowFrom);
					newRegion.setLastRow(targetRowTo);
					currentSheet.addMergedRegion(newRegion);
					mergedList.add(i);
				}
			}
		}

		return pPosition + pEndRow - pStartRow;
	}
    public static void copyRow(HSSFRow srcRow,HSSFRow distRow){
    	distRow.setHeight(srcRow.getHeight());
    	distRow.setHeightInPoints(srcRow.getHeightInPoints());
    	if(distRow.getRowStyle()!=null){
    		distRow.setRowStyle(srcRow.getRowStyle());
    	}
    	distRow.setZeroHeight(srcRow.getZeroHeight());
    }

	/*
	 * 复制单元格
	 */
	public void copyCell(HSSFCell srcCell, HSSFCell distCell) {
		distCell.setCellType(CellType.BLANK);// 清空在设置，以免报错
		distCell.setCellStyle(srcCell.getCellStyle());
		if (srcCell.getCellComment() != null) {
			distCell.setCellComment(srcCell.getCellComment());
		}
		CellType srcCellType = srcCell.getCellType();
		distCell.setCellType(srcCellType);
		// try{
		// distCell.setCellErrorValue(srcCell.getErrorCellValue());
		// }catch (IllegalStateException e) {
		// // TODO: handle exception
		// }
		// try{
		// distCell.setCellFormula(srcCell.getCellFormula());
		// }catch (IllegalStateException e) {
		// // TODO: handle exception
		// }
		// distCell.setCellStyle(srcCell.getCellStyle());
		// distCell.setCellType(srcCell.getCellType());
		// if(distCell.getHyperlink()!=null){
		// distCell.setHyperlink(srcCell.getHyperlink());
		// }
		
		if(CellType.NUMERIC == srcCellType) {
			if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
				distCell.setCellValue(srcCell.getDateCellValue());
			} else {
				distCell.setCellValue(srcCell.getNumericCellValue());
			}
		}else if(CellType.STRING == srcCellType) {		
			distCell.setCellValue(srcCell.getRichStringCellValue());
		}else if(CellType.BLANK == srcCellType) {	
		}else if(CellType.BOOLEAN == srcCellType) {
			distCell.setCellValue(srcCell.getBooleanCellValue());
		}else if(CellType.ERROR == srcCellType) {
			distCell.setCellErrorValue(FormulaError.forInt(srcCell.getErrorCellValue()));
		}else if(CellType.FORMULA == srcCellType) {
			distCell.setCellFormula(srcCell.getCellFormula());
		}
	}

	public void setCellValue(HSSFCell cell, Object obj) {
		cell.setCellType(CellType.BLANK);
		if (obj == null || "".equals(obj.toString())) {
			cell.setCellType(CellType.BLANK);
		} else if (obj instanceof String) {
			cell.setCellType(CellType.STRING);
			cell.setCellValue(obj.toString());
		} else if (obj instanceof Date) {
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue((Date) obj);
		}else if (obj instanceof Boolean){
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue((Boolean) obj);
		}else if (obj instanceof Integer){
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue((Integer) obj);
		}else if (obj instanceof Float){
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue((Float) obj);
		}else if (obj instanceof Double){
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue((Double) obj);
		}else if (obj instanceof Long){
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue((Long) obj);
		}else{
			cell.setCellType(CellType.STRING);
			cell.setCellValue(obj.toString());
		}
	}

	public Object getCellValue(HSSFCell cell) {
		Object value = "";
		CellType cellType = cell.getCellType();
		if(CellType.NUMERIC == cellType) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				value = cell.getDateCellValue();
			} else {
				value = cell.getNumericCellValue();
			}
		}else if(CellType.STRING == cellType) {
			value = cell.getStringCellValue();
		}else if(CellType.BOOLEAN == cellType) {
			value = cell.getBooleanCellValue();
		}else if(CellType.FORMULA == cellType) {
			value = cell.getCellFormula();
		}else if(CellType.ERROR == cellType) {
			value = "";
		}
		return value;
	}

	public void removeRow(HSSFSheet sheet, int rownum) {
		removeRow(sheet, rownum, rownum);
	}

	/**
	 * Remove a row by its index
	 * 
	 * @param sheet
	 *            a Excel sheet
	 * @param rowIndex
	 *            a 0 based index of removing row
	 */
	public void removeRow(HSSFSheet sheet, int startRow, int endRow) {
		int lastRowNum = sheet.getLastRowNum();
		if (startRow >= 0 && endRow > startRow && endRow <= lastRowNum) {
			// 移除合并区域；
			CellRangeAddress region = null;
			for (int i = 0, len = sheet.getNumMergedRegions(); i < len; i++) {
				region = sheet.getMergedRegion(i);
				if ((region.getFirstRow() >= startRow)
						&& (region.getLastRow() <= endRow)) {
					sheet.removeMergedRegion(i);
					i--;
					len--;
				}
			}
			while(lastRowNum-endRow<endRow-startRow+1){
				sheet.createRow(++lastRowNum);
			}
			
			sheet.shiftRows(endRow+1, lastRowNum, startRow - endRow - 1,true,true);// 将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
		}
	}

	public Object getValue(Object obj, String name) {
		Object value = null;
		String[] arr = name.split("\\.");
		if (arr != null && arr.length > 0) {
			name = arr[0].trim();
		}
		if (obj instanceof Map) {
			@SuppressWarnings("unchecked")
			Map<String, Object> map = (Map<String, Object>) obj;
			if (map.containsKey(name)) {
				value = map.get(name);
			} else {
				value = null;
				arr = new String[0];
			}
		} else {
			try {
				value = PropertyUtils.getProperty(obj, name);
			} catch (Exception e) {
				value = null;
				arr = new String[0];
			}
		}
		name = "";
		for (int i = 1; i < arr.length; i++) {
			name += "." + arr[i].trim();
		}
		if (name.length() > 0) {
			return getValue(value, name);
		}
		return value;
	}
}
